<?php
class Classes{
	public function getClass($id){
		$DB = new DB();
		$sql = "SELECT * FROM class where grade_id = '$id'";
		return $DB->fetchAll($sql);
	}
	
	public function getClassByTeacher($id){
		$DB = new DB();
		$sql = "SELECT * FROM class where teacher_id = '$id'";
		
		return $DB->fetchRow($sql);	
	}
	
	public function addClass($title,$grade){
		$DB = new DB();
		$sql = "INSERT INTO `class` VALUES (:id,:teacher,:title,:grade)";
		$field = array(':id' => '',
					   ':teacher' => '',
					   ':title' => $title,
					   ':grade' => $grade
					  );	
		return $DB->updateDB($sql,$field);				  
	}
	
	public function editClass($id,$title,$grade){
		$DB = new DB();
		$sql = "UPDATE `class` set title=?,grade_id=? where id='$id'";
		$field = array($title,$grade);
		return $DB->updateDB($sql,$field);	
	}
	
	public function delClass($id){
		global $db;
		$sql = "delete from `class` where id='$id'";
		return $db->query($sql);	
	}
	
	public function getClassById($id){
		$DB = new DB();
		$sql = "SELECT * FROM class where id = '$id'";

		return $DB->fetchRow($sql);		
	}
	
	public function getAllClasses(){
		$DB = new DB();
		$sql = "SELECT c.*,u.username as teacher,g.title as grade 
				FROM class c
				LEFT JOIN user u ON c.teacher_id = u.id
				LEFT JOIN grade g ON g.id = c.grade_id
				
				";
		
		return $DB->fetchAll($sql);			
	}
	
	public function editTeacher($class_id,$teacher_id){
		$DB = new DB();
		$sql = "UPDATE `class` set teacher_id='$teacher_id' where id='$class_id'";
		
		$field = array();
		return DB::updateDB($sql,$field);
	}
	public function removeTeacher($teacher_id){
		$DB = new DB();
		$sql = "UPDATE `class` set teacher_id='' where teacher_id='$teacher_id'";
		$field = array();
		return DB::updateDB($sql,$field);
	}	
	
	public function getLastId(){
		$DB = new DB();
		$sql = "select max(id) as id from `class`";
		return $DB->fetchRow($sql);		
	}
	
	public function autoClass($id){
		$DB = new DB();
		$sql = "SELECT count( k.class_id ) AS no, class_id
				FROM kid k,class c 
				WHERE k.class_id = c.id
				AND c.grade_id = '$id'
				GROUP BY k.class_id
				ORDER BY no ASC 
				";
		return $DB->fetchRow($sql);	
	}
	
	public function autoTeacher($id){
		$DB = new DB();
		$sql = "SELECT * 
				FROM `class` 
				WHERE teacher_id =0
				And grade_id='$id'
				ORDER BY id ASC";
		return $DB->fetchRow($sql);			
	}
	
	public function insertCs($id,$l){
		$DB = new DB();
		$sql = "INSERT INTO `cs` (id,class_id,lesson) VALUES (:id,:class_id,:lesson)";
		$field = array(':id' => '',
					   ':class_id' => $id,
					   ':lesson' => $l
					  );	
		return $DB->updateDB($sql,$field);		
	}
}
?>